<?php
/*
 * This script converts a MySQL datetime column type to a Unix timestamp.
 * 
 * Since datetime is stored according to the server's local timezone, this uses 
 * PHP's DateTime classes to adjust that time to UTC.  The column is first 
 * converted to VARCHAR, the time is converted, then it is altered to INT type.
 * 
 * Sample output (1st column original value, 2nd column inserted into DB):
 
2011-08-21 12:34:30 1313948070
2011-08-21 12:35:38 1313948138
2011-08-21 12:38:31 1313948311
2011-08-22 11:58:06 1314032286
2011-08-22 16:15:00 1314047700
2011-08-22 16:15:18 1314047718
2011-08-22 16:16:05 1314047765
2011-08-23 01:48:08 1314082088
2011-08-24 15:00:41 1314216041
2011-10-04 13:47:48 1317754068
2011-11-19 04:39:51 1321699191
2011-12-10 03:05:27 1323507927
2011-12-10 03:07:24 1323508044
2011-12-10 03:09:09 1323508149
2011-12-10 03:14:59 1323508499
2012-01-11 01:07:56 1326265676
2012-01-11 01:42:08 1326267728
2012-01-11 01:46:08 1326267968
2012-01-11 04:27:19 1326277639
2012-01-12 02:59:55 1326358795
2012-01-12 03:05:14 1326359114
2012-01-12 03:07:12 1326359232
2012-01-15 22:26:25 1326687985
2012-02-20 09:33:52 1329752032
 
 * 2012 Ameir Abdeldayem
 * http://www.ameir.net
 */

$mysql_host = "localhost";
$mysql_database = "dbname";
$mysql_user = "username";
$mysql_password = "password";

$table_name = 'table_to_update';
$column_name = 'column_to_operate_on';
$server_timezone = date_default_timezone_get();  // shouldn't need to update this

$connect = mysql_connect($mysql_host, $mysql_user, $mysql_password) or die("error");
mysql_select_db($mysql_database) or die(mysql_error());


if (!mysql_query("ALTER TABLE `$table_name` CHANGE `$column_name` `$column_name` VARCHAR( 64 ) NOT NULL"))
    die("Could not alter table\n" . mysql_error());


$results = mysql_query("SELECT `id`,`$column_name` FROM `$table_name`");
echo mysql_num_rows($results);
while ($datetime = mysql_fetch_row($results)) {
    list ($id, $timestamp) = $datetime;
    // print_r($datetime);
    echo "$timestamp ";
    if (is_int($timestamp))
        continue;
    try {
        $date = new DateTime($timestamp, new DateTimeZone($server_timezone));
    } catch (Exception $e) {
        //  echo "Error $e";
        echo "  (failed)\n";
        continue;
    }
    $time = $date->format('U') . "\n";
    echo $time . "\n";


    if (!mysql_query("UPDATE `$table_name` SET `$column_name` = $time WHERE `id` = $id"))
        echo "Could not update id $id ($timestamp/$time)\n" . mysql_error();
}

if (!mysql_query("ALTER TABLE `$table_name` CHANGE `$column_name` `$column_name` INT( 11 ) NOT NULL"))
    die("Could not alter table\n" . mysql_error());
	
?>
